using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SQLite;


namespace Web4
{

    public class OrderService
    {
        public SQLiteConnection Sql;

        
        public SQLiteConnection open()
        {
            //数据库地址 字符串

            string FilePath = @"Data Source= D:\csharpDB\orders.db";
            //new 数据库对象
            Sql = new SQLiteConnection(FilePath);
            //打开数据库
            Sql.Open();
            //返回数据库连接对象
            return Sql;
        }
        public Order id_getOrder(int id)
        {
            Order a=new Order();
            open();
            try
            {
                String strSql = "SELECT * FROM [orders] WHERE id=" + id;
                SQLiteCommand cmd = new SQLiteCommand(strSql, Sql);
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string pro = Convert.ToString(dr["product"]);
                    string cus = Convert.ToString(dr["customer"]);
                    double pri = Convert.ToDouble(dr["price"]);
                    a.setOrderNum(id);
                    a.setProduct(pro);
                    a.setCustomer(cus);
                    a.setPrice(pri);
                }
                return a;
            }
            catch
            {
                return null;
            }
            
        }

        public List<Order> pro_getOrders(String pro)
        {
            List<Order> orderList=null;
            open();
            try
            {
                String strSql = "SELECT * FROM [orders] WHERE product=" + pro;
                SQLiteCommand cmd = new SQLiteCommand(strSql, Sql);
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Order a = new Order();
                    int id = Convert.ToInt32(dr["id"]);
                    string cus = Convert.ToString(dr["customer"]);
                    double pri = Convert.ToDouble(dr["price"]);
                    a.setOrderNum(id);
                    a.setProduct(pro);
                    a.setCustomer(cus);
                    a.setPrice(pri);
                    orderList.Add(a);
                }
                return orderList;
            }
            catch
            {
                return null;
            }
            

        }

        public String addOrder(int id,string pro,string cus,double pri)
        {
            open();
            try
            {
                string strSql = $"insert into orders(id,product,customer,price) values({id},'{pro}','{cus}',{pri})";
                SQLiteCommand cmd = new SQLiteCommand(strSql, Sql);
                Order a = new Order(id,pro,cus,pri);
                return "成功添加订单"+a.ToString();
            }catch
            {
                //MessageBox.Show(e.ToString());
                return "error";
            }
            
        }
        public string updateOrder(int id, string pro, string cus, double pri)
        {
            open();
            try
            {
                string strSql = $"update orders set product='{pro}',customer='{cus}',price={pri} where id={id}";
                SQLiteCommand cmd = new SQLiteCommand(strSql, Sql);
                Order a = new Order(id, pro, cus, pri);
                return "成功更新订单："+a.ToString();
            }
            catch
            {
                return "error";
            }
            
        }

        public String deleteOrder(int id)
        {
            open();
            //Order a = id_getOrder(id);
            try
            {
                string strSql = $"delete * from order where id={id}";
                SQLiteCommand cmd = new SQLiteCommand(strSql, Sql);           
                return "成功删除了id为"+id+"的订单";
            }
            catch
            {
                return "error";
            }
           
        }
    }
}